package com.cennavi.mineplay.gisfile;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class CsvReader {

	private String tableName;

	private Connection conn;
	
	private List<String> columnNames = new ArrayList<>();
	private List<String> columnTypes = new ArrayList<>();
	
	private List<List<String>> values = new ArrayList<>();
	
	private int geomIndex = -1;
	
	private String geomType;
	
	
	public CsvReader(String tableName,Connection conn,String csvFile,String geomType) throws FileNotFoundException{
		this.tableName = tableName;
		this.conn = conn;
		this.geomType = geomType;
		
		Scanner scanner = new Scanner(new FileInputStream(csvFile));
		String columns[] = scanner.nextLine().split(",");
		int i=0;
		for(String col : columns)
		{
			columnNames.add(col);
			i++;
			values.add(new ArrayList<String>());
			columnTypes.add("DOUBLE");
			if ("geom".equals(col))
				geomIndex = i-1;
		}
		
		
		
		while(scanner.hasNextLine()){
			String[] splits = scanner.nextLine().split(",");
			for(int j=0;j<splits.length;j++){
				
				String v = splits[j];
				
				try {
					Double.parseDouble(v);
				} catch (NumberFormatException e) {
					columnTypes.set(j, "String");
				}
				
				values.get(j).add(splits[j]);
			}
		}
		
		columnTypes.set(geomIndex, "geometry");
		
		scanner.close();
		
	}
	
	public void createTable() throws Exception {

		StringBuilder sqlSB = new StringBuilder("create table ");
		sqlSB.append(tableName);
		sqlSB.append("(");
		for (int i = 0; i < columnNames.size(); i++) {
			String column = columnNames.get(i);
			String type = columnTypes.get(i);
			if (i>0)
				sqlSB.append(",");
			sqlSB.append(column);
			if ("INTEGER".equals(type)) {
				sqlSB.append(" int");
			} else if ("DOUBLE".equals(type)) {
				sqlSB.append(" numeric");
			} else if ("geometry".equals(type)){
				sqlSB.append(" geometry");
			} else {
				sqlSB.append(" character varying(1000)");
			}
		}
		sqlSB.append(")");
		Statement stmt = conn.createStatement();
		stmt.execute(sqlSB.toString());
		stmt.close();

	}
	
	
	public void batchInsert() throws Exception {

		StringBuilder sql = new StringBuilder("insert into ");
		sql.append(tableName);
		sql.append("(");
		for (int i = 0; i < columnNames.size(); i++) {
			if (i >0)
				sql.append(",");
			sql.append(columnNames.get(i));
		}
		sql.append(") values (");
		for (int i = 0; i < columnNames.size(); i++) {
			
			String cname = columnNames.get(i);
			
			if (!"geom".equals(cname)){
			
				if (i>0)
					sql.append(",?");
				else
					sql.append("?");
				
			}else{
				if (i >0)
					sql.append(",st_geomfromtext(?,4326)");
				else
					sql.append("st_geomfromtext(?,4326)");
			}
		}
		sql.append(")");

		
		PreparedStatement pstmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < values.get(0).size(); i++) {
			
			List<String> rows = new ArrayList<String>();
			
			for(int j=0;j<values.size();j++){
				rows.add(values.get(j).get(i));
			}
			
			

			for (int j = 0; j < columnTypes.size(); j++) {
				String type = columnTypes.get(j);
				 if ("DOUBLE".equals(type)) {
					pstmt.setDouble(j + 1, Double.parseDouble(rows.get(j)));
				}else if ("geometry".equals(type)){
					
					String wkt = null;
					
					if ("point".equals(geomType)){
						wkt = "Point("+rows.get(j)+")";
					}else if ("line".equals(geomType)){
						wkt = "LineString("+rows.get(j).replaceAll(";", ",")+")";
					}else{
						wkt = "Polygon(("+rows.get(j).replaceAll(";", ",")+"))";
					}
					
					pstmt.setString(j+1, wkt);
				} else {
					Object v = rows.get(j);
					if (v != null) {
						if (v.toString().length() > 300)
							pstmt.setString(j + 1,
									v.toString().substring(0, 300));
						else
							pstmt.setString(j + 1, v.toString());
					} else {
						pstmt.setString(j + 1, null);
					}
				}
			}

			pstmt.addBatch();

			if (i % 501 == 0)
				pstmt.executeBatch();
		}

		pstmt.executeBatch();

		pstmt.close();
	}
}
